package com.ruoyi.system.service.impl;

import cn.hutool.core.util.ObjectUtil;
import com.alibaba.excel.EasyExcel;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.ruoyi.common.core.domain.AjaxResult;
import com.ruoyi.common.core.domain.R;
import com.ruoyi.common.enums.CrawlerStatusType;
import com.ruoyi.common.enums.CustomerType;
import com.ruoyi.common.enums.ImportType;
import com.ruoyi.common.exception.base.BaseException;
import com.ruoyi.system.async.DataStatistics;
import com.ruoyi.system.common.CQReadExcelListener;
import com.ruoyi.system.common.OtherReadExcelListener;
import com.ruoyi.system.domain.CrmCustomerInfo;
import com.ruoyi.system.domain.CrmCustomerUserInfo;
import com.ruoyi.system.domain.from.CrmCustomerUserInfoOther;
import com.ruoyi.system.domain.from.CustomerDetailsExport;
import com.ruoyi.system.domain.from.CustomerDetailsInfoPage;
import com.ruoyi.system.domain.from.ReptileFrom;
import com.ruoyi.system.domain.vo.CrmCustomerExportDTO;
import com.ruoyi.system.domain.vo.CustomerDetailsPageVo;
import com.ruoyi.system.domain.vo.StartEndSum;
import com.ruoyi.system.mapper.CrmCustomerInfoMapper;
import com.ruoyi.system.mapper.CrmCustomerUserInfoMapper;
import com.ruoyi.system.service.ICrmCustomerInfoService;
import com.ruoyi.system.service.ICrmCustomerUserService;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.support.TransactionSynchronizationAdapter;
import org.springframework.transaction.support.TransactionSynchronizationManager;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.Period;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

@Service
@Slf4j
public class CrmCustomerUserService extends ServiceImpl<CrmCustomerUserInfoMapper, CrmCustomerUserInfo> implements ICrmCustomerUserService {

    @Autowired
    private ICrmCustomerInfoService crmCustomerInfoService;

    @Autowired
    private CrmCustomerInfoMapper crmCustomerInfoMapper;
    @Autowired
    private DataStatistics dataStatistics;


    @Override
    @Transactional(rollbackFor = Exception.class)
    public AjaxResult crmCustomerUserImportExcel(Long customerId, List<MultipartFile> files) throws Exception {
        CrmCustomerInfo crmCustomerInfo = crmCustomerInfoService.getById(customerId);
        if (ObjectUtil.isNull(crmCustomerInfo)) {
            return AjaxResult.error("客户信息不存在");
        }
        if (!crmCustomerInfo.getDataProcessing().equals(ImportType.TOBEIMPROTINGDATA.getImprotTypeCode())) {
            return AjaxResult.error("该数据正在导入或者该客户为意向客户只能导入一次");
        }
        List<CrmCustomerUserInfo> crmCustomerUserInfoList = new ArrayList<>();

        //设置数据正在导入
        crmCustomerInfo.setDataProcessing(ImportType.IMPROTINGDATA.getImprotTypeCode());
        crmCustomerInfoService.updateById(crmCustomerInfo);
        if ("重庆市".equals(crmCustomerInfo.getProvinceName())) {
            //TODO数据校验
            for (MultipartFile file : files) {
                CQReadExcelListener readListener = new CQReadExcelListener();
                EasyExcel.read(file.getInputStream(), CrmCustomerUserInfo.class, readListener).sheet().doRead();
                List<CrmCustomerUserInfo> data = readListener.getData();
                crmCustomerUserInfoList.addAll(data);
            }
            for (CrmCustomerUserInfo crmCustomerUserInfo : crmCustomerUserInfoList) {
                crmCustomerUserInfo.setCustomerId(customerId);
            }
        } else {
            CrmCustomerUserInfoOther(customerId, files, crmCustomerUserInfoList);
        }
        //确保事物提交后进行异步任务
        TransactionSynchronizationManager.registerSynchronization(new TransactionSynchronizationAdapter() {
            @SneakyThrows
            @Override
            public void afterCommit() {
                dataStatistics.dataStatistics(customerId, crmCustomerUserInfoList, "import");
            }
        });
        return AjaxResult.success();
        //TODO数据异步处理入库
    }

    /**
     * 其他导入
     */
    public void CrmCustomerUserInfoOther(Long customerId, List<MultipartFile> files, List<CrmCustomerUserInfo> crmCustomerUserInfoList) throws IOException, ParseException {
        for (MultipartFile uploadFile : files) {
            List<CrmCustomerUserInfoOther> readExcelListenerData;
            OtherReadExcelListener readExcelListener = new OtherReadExcelListener();
            EasyExcel.read(uploadFile.getInputStream(), CrmCustomerUserInfoOther.class, readExcelListener).sheet().doRead();
            readExcelListenerData = readExcelListener.getData();
            String feesDate="";
            Boolean bool=false;
            if (ObjectUtil.isNotNull(readExcelListenerData.get(0).getUserName())&&readExcelListenerData.get(0).getUserName().contains("台账年月")){
                bool=true;
                String regex = "\\d{4}年\\d{2}月"; // 匹配格式为XXXX年XX月的日期
                Pattern pattern = Pattern.compile(regex);
                Matcher matcher = pattern.matcher(readExcelListenerData.get(0).getUserName());
                if (matcher.find()) {
                    String group = matcher.group();
                    DateFormat inputDateFormat = new SimpleDateFormat("yyyy年MM月");
                    Date date = inputDateFormat.parse(group);
                    DateFormat outputDateFormat = new SimpleDateFormat("yyyyMM");
                    feesDate= outputDateFormat.format(date);
                    //移除第一张
                } else {
                    throw new BaseException("台账年月异常");
                }

                if (ObjectUtil.isNotNull(readExcelListenerData.get(0).getUserIdentityCardNumber())&&readExcelListenerData.get(0).getUserIdentityCardNumber().contains("企业名称")){
                    String patternName = "企业名称：(.*)";
                    Pattern regexName = Pattern.compile(patternName);
                    Matcher matcherNmae = regexName.matcher(readExcelListenerData.get(0).getUserIdentityCardNumber());
                    if (matcherNmae.find()) {
                        String companyName = matcherNmae.group(1);
                        CrmCustomerInfo crmCustomerInfo = crmCustomerInfoService.getById(customerId);
                        String crmCustomerInfoCompanyName = crmCustomerInfo.getCompanyName();
                        if (!crmCustomerInfoCompanyName.equals(companyName)){
                            throw new BaseException("企业名称与客户名称不一致");
                        }
                    }else {
                        throw new BaseException("企业名称不能为空");
                    }
                }
                readExcelListenerData.remove(0);
            }
            if (!bool){
                for (CrmCustomerUserInfoOther crmCustomerUserInfoOther : readExcelListenerData) {
                    CrmCustomerUserInfo crmCustomerUserInfo = CrmCustomerUserInfo.builder()
                            .customerId(customerId)
                            .userName(crmCustomerUserInfoOther.getUserName())
                            .userIdentityCardNumber(crmCustomerUserInfoOther.getUserIdentityCardNumber())
                            .feesDate(crmCustomerUserInfoOther.getFeesDate())
                            .paymentType("正常应缴")
                            .paymentBase(1000.00)
                            .build();
                    crmCustomerUserInfoList.add(crmCustomerUserInfo);
                }
            }else {
                //移除第一行
                readExcelListenerData.remove(0);
                for (CrmCustomerUserInfoOther crmCustomerUserInfoOther : readExcelListenerData) {
                    CrmCustomerUserInfo crmCustomerUserInfo = CrmCustomerUserInfo.builder()
                            .customerId(customerId)
                            .userName(crmCustomerUserInfoOther.getUserName())
                            .userIdentityCardNumber(crmCustomerUserInfoOther.getUserIdentityCardNumber())
                            .feesDate(feesDate)
                            .paymentType("正常应缴")
                            .paymentBase(1000.00)
                            .build();
                    crmCustomerUserInfoList.add(crmCustomerUserInfo);
                }
            }
        }
    }

    /**
     * 外地的第二种模板
     * @param inputStream
     * @return
     * @throws IOException
     */
    public List<CrmCustomerUserInfoOther> CrmCustomerUserInfoOtherTem(InputStream inputStream) throws IOException, ParseException {
        List<CrmCustomerUserInfoOther> list=new ArrayList<>();
        Workbook workbook = new XSSFWorkbook(inputStream);
        Sheet sheet = workbook.getSheetAt(0);
        // 遍历工作表中的行
        int rowCount = 0;  // 计数器变量
        //日期
        String feesDate=null;
        //sheet循环
        for (Row row : sheet) {
            rowCount++;
            // 跳过前两行
            if (rowCount <= 1) {
                continue;
            }
            //从第二行获取
            if (rowCount==2){
                Cell input = row.getCell(0);
                String stringCellValue = input.getStringCellValue();
                String regex = "\\d{4}年\\d{2}月"; // 匹配格式为XXXX年XX月的日期
                Pattern pattern = Pattern.compile(regex);
                Matcher matcher = pattern.matcher(stringCellValue);
                if (matcher.find()) {
                    String group = matcher.group();
                    DateFormat inputDateFormat = new SimpleDateFormat("yyyy年MM月");
                    Date date = inputDateFormat.parse(group);
                    DateFormat outputDateFormat = new SimpleDateFormat("yyyyMM");
                    feesDate= outputDateFormat.format(date);
                } else {
                    throw new BaseException("台账年月异常");
                }

            }
            CrmCustomerUserInfoOther crmCustomerUserInfo = CrmCustomerUserInfoOther.builder()
                    .feesDate(feesDate)
                    .build();
            //从第三行开始提取数据
            int lastCellNum = row.getLastCellNum();
            for (int i = 0; i < lastCellNum; i++) {
                Cell cell = row.getCell(i); // 获取指定索引的单元格
                if (cell != null) {
                    // 根据单元格类型读取数据
                    CellType cellType = cell.getCellType();
                    if (cellType == CellType.STRING) {
                        String cellValue = cell.getStringCellValue();
                        if (i==0){
                            crmCustomerUserInfo.setUserName(cellValue);
                        }else {
                            crmCustomerUserInfo.setUserIdentityCardNumber(cellValue);
                        }
                    } else if (cellType == CellType.NUMERIC) {
                        double cellValue = cell.getNumericCellValue();
                        if (i==0){
                            crmCustomerUserInfo.setUserName(String.valueOf(cellValue));
                        }else {
                            crmCustomerUserInfo.setUserIdentityCardNumber(String.valueOf(cellValue));
                        }
                    }
                }
            }
            list.add(crmCustomerUserInfo);
        }
        return list;
    }
    /**
     * 查询客户明细列表
     */
    @Override
    public R<CustomerDetailsPageVo> crmCustomerUserPage(Long customerId, CustomerDetailsInfoPage crmCustomerInfoPage) {
        CrmCustomerInfo crmCustomerInfo = crmCustomerInfoService.getById(customerId);
        if (ObjectUtil.isNull(crmCustomerInfo)) {
            return R.fail("客户信息不存在");
        }
        QueryWrapper<CrmCustomerUserInfo> crmCustomerUserInfoQueryWrapper = new QueryWrapper<CrmCustomerUserInfo>().eq("customer_id", customerId)
                .like(ObjectUtil.isNotEmpty(crmCustomerInfoPage.getUserName()), "user_name", crmCustomerInfoPage.getUserName())
                .like(ObjectUtil.isNotEmpty(crmCustomerInfoPage.getUserIdentityCardNumber()), "user_identity_card_number", crmCustomerInfoPage.getUserIdentityCardNumber())
                .orderByDesc("gmt_create");
        QueryWrapper<CrmCustomerUserInfo> customerUserInfoQueryWrapper = queryWrapperCopy(crmCustomerUserInfoQueryWrapper, crmCustomerInfoPage);
        CustomerDetailsInfoPage customerDetailsInfoPage = this.page(crmCustomerInfoPage,customerUserInfoQueryWrapper);
        CustomerDetailsPageVo customerDetailsPageVo = CustomerDetailsPageVo
                .builder()
                .total(customerDetailsInfoPage.getTotal())
                .size(customerDetailsInfoPage.getSize())
                .current(customerDetailsInfoPage.getCurrent())
                .records(customerDetailsInfoPage.getRecords())
                .build();
        return R.ok(customerDetailsPageVo);
    }

    public QueryWrapper<CrmCustomerUserInfo> queryWrapperCopy(QueryWrapper<CrmCustomerUserInfo> crmCustomerUserInfoQueryWrapper,CustomerDetailsInfoPage crmCustomerInfoPage){
        if (ObjectUtil.isNotEmpty(crmCustomerInfoPage.getCrawlerDataType())){
            if (crmCustomerInfoPage.getCrawlerDataType()){
                crmCustomerUserInfoQueryWrapper.eq("crawler_data","true");
            }else {
                crmCustomerUserInfoQueryWrapper.ne("crawler_data","true");
            }
        }
        if (ObjectUtil.isNotEmpty(crmCustomerInfoPage.getIdentityCheckType())){
            if (crmCustomerInfoPage.getIdentityCheckType()){
                crmCustomerUserInfoQueryWrapper.eq("identity_check_msg","true");
            }else {
                crmCustomerUserInfoQueryWrapper.ne("identity_check_msg","true");
            }
        }

        if (ObjectUtil.isNotEmpty(crmCustomerInfoPage.getRetiredCrawlerType())){
            if (crmCustomerInfoPage.getRetiredCrawlerType()){
                crmCustomerUserInfoQueryWrapper.eq("retired_crawler_data","true");
            }else {
                crmCustomerUserInfoQueryWrapper.ne("retired_crawler_data","true");
            }
        }
        return crmCustomerUserInfoQueryWrapper;
    }

    /**
     * 按钮控制
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public R reptile(ReptileFrom reptileFrom) {
        CrmCustomerInfo crmCustomerInfo = crmCustomerInfoService.getById(reptileFrom.getCustomerId());
        if (ObjectUtil.isNull(crmCustomerInfo)) {
            return R.fail("客户信息不存在");
        }
        if (ImportType.IMPROTINGDATA.getImprotTypeCode().equals(crmCustomerInfo.getDataProcessing())) {
            return R.fail("正在导入中不能进行筛查");
        }
        if (crmCustomerInfo.getCrawlerStatus().equals(CrawlerStatusType.CRAWLING.getCrawlerStatusTypeCode())) {
            return R.fail("正在进行脱贫筛查或者失业就业筛查或者退役筛查不能重复操作");
        }
        crmCustomerInfo.setCrawlerStatus(CrawlerStatusType.CRAWLING.getCrawlerStatusTypeCode());
        crmCustomerInfoService.updateById(crmCustomerInfo);
        if (reptileFrom.getReptileType().equals("identity") || reptileFrom.getReptileType().equals("unemployment")
                || reptileFrom.getReptileType().equals("all") || reptileFrom.getReptileType().equals("retired") || reptileFrom.getReptileType().equals("reptilallchick")) {
            //确保事物提交后进行异步任务
            TransactionSynchronizationManager.registerSynchronization(new TransactionSynchronizationAdapter() {
                @SneakyThrows
                @Override
                public void afterCommit() {
                    dataStatistics.dataStatistics(reptileFrom.getCustomerId(), new ArrayList<>(), reptileFrom.getReptileType());
                }
            });
            return R.ok();
        } else {
            return R.fail("爬虫类型错误");
        }
    }

    @Override
    public R deleteCrmCustomerId(Long id) {
        CrmCustomerUserInfo crmCustomerUserInfo = this.getById(id);
        CrmCustomerInfo crmCustomerInfo = crmCustomerInfoService.getById(crmCustomerUserInfo.getCustomerId());
        if (ObjectUtil.isNull(crmCustomerInfo)) {
            return R.fail("客户信息不存在,无法删除");
        }
        if (ImportType.IMPROTINGDATA.getImprotTypeCode().equals(crmCustomerInfo.getDataProcessing())) {
            return R.fail("正在导入中不能删除");
        }
        if (crmCustomerInfo.getCrawlerStatus().equals(CrawlerStatusType.CRAWLING.getCrawlerStatusTypeCode())) {
            return R.fail("正在进行脱贫筛查或者失业就业筛查或者退役筛查不能删除");
        }
        this.removeById(id);
        return R.ok(true,"删除成功");
    }

    @Override
    public List<Map<String, String>> export(CustomerDetailsExport customerDetailsExport) {
        CrmCustomerInfo crmCustomerInfo = crmCustomerInfoService.getById(customerDetailsExport.getCustomerId());
        if (ObjectUtil.isNull(crmCustomerInfo)) {
            throw new RuntimeException("客户信息不存在");
        }
        List<CrmCustomerExportDTO> crmCustomerExportDTOS=null;

        if (customerDetailsExport.getReptileType().equals("identity") || customerDetailsExport.getReptileType().equals("unemployment") || customerDetailsExport.getReptileType().equals("retired")) {
            crmCustomerExportDTOS = crmCustomerInfoMapper.selectAnalystBew(customerDetailsExport);
        } else if (customerDetailsExport.getReptileType().equals("eligible")){
            crmCustomerExportDTOS= crmCustomerInfoMapper.selectAnalystEligible(customerDetailsExport);
        }else if (customerDetailsExport.getReptileType().equals("all")){
            crmCustomerExportDTOS= crmCustomerInfoMapper.selectAnalystAll(customerDetailsExport);
        }else {
            throw new RuntimeException("数据筛选类型错误");
        }
        //按身份证号码和月份去重
        for (CrmCustomerExportDTO crmCustomerExportDTO : crmCustomerExportDTOS) {
            if (crmCustomerExportDTO.getPaymentBase()>0){
                crmCustomerExportDTO.setPaymentBase(1);
            }else if (crmCustomerExportDTO.getPaymentBase()<0){
                crmCustomerExportDTO.setPaymentBase(-1);
            }else {
                crmCustomerExportDTO.setPaymentBase(0);
            }
        }
        // 查询最大年份和最小年份
        Integer maxYear = 0;
        Integer minYear = 3000;

        Iterator<CrmCustomerExportDTO> iterator = crmCustomerExportDTOS.iterator();
        while (iterator.hasNext()) {
            CrmCustomerExportDTO userInfo = iterator.next();
            String feesDate = userInfo.getFeesDate();
            Integer year = Integer.parseInt(feesDate.substring(0, 4));
            if (year < 2019) {
                iterator.remove();
                continue;
            }
            if (maxYear < year) {
                maxYear = year;
            }
            if (minYear > year) {
                minYear = year;
            }
        }

        // 分析数据
        Map<String, Map<String, String>> row = new HashMap<>();
//        Map<String, List<CrmCustomerExportDTO>> stringListMap=null;
        Map<String, List<CrmCustomerExportDTO>> stringListMap = crmCustomerExportDTOS.stream().collect(Collectors.groupingBy(CrmCustomerExportDTO::getUserIdentityCardNumber));
        //每列合计
        Map<String, String> mapTotal=mapTotalGet(customerDetailsExport.getReptileType(),maxYear,minYear);
        // 遍历每个人的数据
        for (CrmCustomerExportDTO userInfo : crmCustomerExportDTOS) {
            // 初始化
            String deptNumber = userInfo.getDeptNumber();
            String userNumber = userInfo.getUserNumber();
            String userName = userInfo.getUserName();
            String userIdentityCardNumber = userInfo.getUserIdentityCardNumber();
            Integer paymentBase = userInfo.getPaymentBase();
            Integer userAge = userInfo.getUserAge();
            Integer userGender = userInfo.getUserGender();
            String identityCheckDocument = userInfo.getIdentityCheckDocument();
            String identityCheckDate = userInfo.getIdentityCheckDate();
            if (row.get(deptNumber + "-" + userNumber + "-" + userName + "-" + userIdentityCardNumber) == null) {
                Map<String, String> map = new LinkedHashMap<>();
                if (customerDetailsExport.getReptileType().equals("eligible")){
                    map.put("deptNumber", deptNumber);
                    map.put("userNumber", userNumber);
                    map.put("userName", userName);
                    map.put("userIdentityCardNumber", userIdentityCardNumber);
                    map.put("userAge",userAge.toString());
                    if (userGender==1){
                        map.put("userGender","男");
                    }else {
                        map.put("userGender","女");
                    }
                    //取值开始时间和结束时间 以及合计
                    StartEndSum startEndSum = startEndSum(userIdentityCardNumber, stringListMap);
                    map.put("startDate",startEndSum.getStartDate());
                    map.put("endDate",startEndSum.getEndDate());
                    map.put("sum",startEndSum.getSum().toString());
                }else if (customerDetailsExport.getReptileType().equals("unemployment")){
                    map.put("deptNumber", deptNumber);
                    map.put("userNumber", userNumber);
                    map.put("userName", userName);
                    map.put("userIdentityCardNumber", userIdentityCardNumber);
                    map.put("identityCheckDocument", identityCheckDocument);
                    map.put("identityCheckDate", identityCheckDate);
                    //取值开始时间和结束时间 以及合计
                    StartEndSum startEndSum = startEndSum(userIdentityCardNumber, stringListMap);
                    map.put("startDate",startEndSum.getStartDate());
                    map.put("endDate",startEndSum.getEndDate());
                    map.put("sum",startEndSum.getSum().toString());
                    List<CrmCustomerExportDTO> exportDTOList = stringListMap.get(userIdentityCardNumber);
                    if (exportDTOList!=null && exportDTOList.size()>0){
                        List<CrmCustomerExportDTO> customerExportDTOS = exportDTOList.stream().sorted(Comparator.comparing(CrmCustomerExportDTO::getFeesDate)).collect(Collectors.toList());
                        CrmCustomerExportDTO crmCustomerExportDTO = customerExportDTOS.get(0);
                        Boolean aBoolean = dateComparisonExample(identityCheckDate, crmCustomerExportDTO.getFeesDate());
                        if (aBoolean){
                            map.put("sixTrue", "有效数据");
                        }else {
                            map.put("sixTrue", "无效数据");
                        }
                    }
                }else {
                    map.put("deptNumber", deptNumber);
                    map.put("userNumber", userNumber);
                    map.put("userName", userName);
                    map.put("userIdentityCardNumber", userIdentityCardNumber);
                    StartEndSum startEndSum = startEndSum(userIdentityCardNumber, stringListMap);
                    map.put("startDate",startEndSum.getStartDate());
                    map.put("endDate",startEndSum.getEndDate());
                    map.put("sum",startEndSum.getSum().toString());
                }
                // 初始化全部的列为0
                for (int i = minYear; i <= maxYear; i++) {
                    // 每月数据
                    for (int j = 1; j <= 12; j++) {
                        String monthStr = "";
                        if (j < 10) {
                            monthStr = "0" + j;
                        } else {
                            monthStr = "" + j;
                        }
                        String fees = i + monthStr;
                        map.put(fees, "0");
                    }
                    map.put(i + "合计", "0");
                }
                row.put(deptNumber + "-" + userNumber + "-" + userName + "-" + userIdentityCardNumber, map);
            }

            // 更新数据
            String feesDate = userInfo.getFeesDate();
            Map<String, String> map = row.get(deptNumber + "-" + userNumber + "-" + userName + "-" + userIdentityCardNumber);
            map.put(feesDate, paymentBase + "");
            if (ObjectUtil.isNull(mapTotal.get(feesDate))){
                mapTotal.put(feesDate,paymentBase.toString());
            }else {
                String paymentBaseGet = mapTotal.get(feesDate);
                int paymentBaseNew=Integer.parseInt(paymentBaseGet)+paymentBase;
                mapTotal.put(feesDate, Integer.toString(paymentBaseNew));
            }
        }

        // 计算合计
        Integer finalMinYear = minYear;
        Integer finalMaxYear = maxYear;
        row.forEach((users, map) -> {
            for (int i = finalMinYear; i <= finalMaxYear; i++) {
                Integer sum = 0;
                // 每月数据
                for (int j = 1; j <= 12; j++) {
                    String monthStr = "";
                    if (j < 10) {
                        monthStr = "0" + j;
                    } else {
                        monthStr = "" + j;
                    }
                    String fees = i + monthStr;
                    sum += Integer.parseInt(map.get(fees));
                }
                map.put(i + "合计", sum + "");

                if (ObjectUtil.isNull(mapTotal.get(i + "合计"))){
                    mapTotal.put(i + "合计",sum.toString());
                }else {
                    String paymentBaseGet = mapTotal.get(i + "合计");
                    int paymentBaseNew=Integer.parseInt(paymentBaseGet)+sum;
                    mapTotal.put(i + "合计", Integer.toString(paymentBaseNew));
                }
            }
        });

        // 转换格式
        List<Map<String, String>> list = new ArrayList<>();
        for (String key : row.keySet()) {
            list.add(row.get(key));
        }
        if (crmCustomerExportDTOS.size()>0){
            list.add(mapTotal);
        }
        return list;

//        QueryWrapper<CrmCustomerUserInfo> customerUserInfoQueryWrapper = new QueryWrapper<CrmCustomerUserInfo>().eq("customer_id", customerId);
//        List<CrmCustomerUserInfo> customerUserInfos = this.list(customerUserInfoQueryWrapper);
//        ExcelUtil<CrmCustomerUserInfo> util = new ExcelUtil<>(CrmCustomerUserInfo.class);
//        util.exportExcel(response, customerUserInfos, "客户详情");
    }

    /**
     *
     * @throws IOException
     */
    public Boolean dateComparisonExample(String dateStr1,String dateStr2){

        // 创建日期格式化器
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy年MM月dd日");

        // 解析字符串并转换成 LocalDate 对象
        LocalDate date1 = LocalDate.parse(dateStr1, formatter);
        LocalDate date2 = formatDate(dateStr2);
        // 比较两个日期
        if (date1.isAfter(date2)) {
            return false;
        } else if (date1.isBefore(date2)) {
            Period period = Period.between(date1, date2);
            int months = period.getMonths();
            int years = period.getYears();
            return (years * 12 + months) > 6;
        } else {
            return false;
        }
    }


    public static LocalDate formatDate(String input) {
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("uuuuMMdd");
        LocalDate date = LocalDate.parse(input + "01", formatter);
        return date;
    }
    //取值开始时间和结束时间 以及合计
    public StartEndSum startEndSum(String userIdentityCardNumber,Map<String, List<CrmCustomerExportDTO>> stringListMap){
        List<CrmCustomerExportDTO> exportDTOList = stringListMap.get(userIdentityCardNumber);
        List<CrmCustomerExportDTO> customerExportDTOS = exportDTOList.stream().filter(crmCustomerExportDTO -> crmCustomerExportDTO.getPaymentBase() > 0).sorted(Comparator.comparing(CrmCustomerExportDTO::getFeesDate)).collect(Collectors.toList());
        String startDate=null;
        String endDate=null;
        if ( customerExportDTOS.size() > 0){
            startDate = customerExportDTOS.get(0).getFeesDate();
            endDate=customerExportDTOS.get(customerExportDTOS.size()-1).getFeesDate();
        }
        HashMap<String,Integer> hashMap=new HashMap<>();
        int sum = 0;
        //合计
        for (CrmCustomerExportDTO crmCustomerExportDTO : exportDTOList) {
            if (ObjectUtil.isNull(hashMap.get(crmCustomerExportDTO.getFeesDate()))){
                hashMap.put(crmCustomerExportDTO.getFeesDate(),crmCustomerExportDTO.getPaymentBase());
            }else {
                Integer integer = hashMap.get(crmCustomerExportDTO.getFeesDate());
                hashMap.put(crmCustomerExportDTO.getFeesDate(),crmCustomerExportDTO.getPaymentBase()+integer);
            }
        }

        for (Map.Entry<String, Integer> entry : hashMap.entrySet()) {
            Integer value = entry.getValue();
            if (value <0){
                sum+=-1;
            }else if (value >0){
                sum+=1;
            }else {
                sum+=0;
            }
        }
        return StartEndSum.builder().startDate(startDate).endDate(endDate).sum(sum).build();
    }

    //给最后一列添加数据

    public Map<String, String> mapTotalGet(String type,Integer maxYear,Integer minYear){
        //每列合计
        Map<String, String> mapTotal=new LinkedHashMap<>();
        if (type.equals("eligible")){
            mapTotal.put("deptNumber", "");
            mapTotal.put("userNumber", "");
            mapTotal.put("userName", "");
            mapTotal.put("userIdentityCardNumber", "");
            mapTotal.put("userAge","");
            mapTotal.put("userGender","");
            mapTotal.put("startDate","");
            mapTotal.put("endDate","");
            mapTotal.put("sum","合计：");
        }else if (type.equals("unemployment")){
            mapTotal.put("deptNumber", "");
            mapTotal.put("userNumber", "");
            mapTotal.put("userName", "");
            mapTotal.put("userIdentityCardNumber", "");
            mapTotal.put("identityCheckDocument", "");
            mapTotal.put("identityCheckDate", "");
            mapTotal.put("startDate","");
            mapTotal.put("endDate","");
            mapTotal.put("sixTrue","");
            mapTotal.put("sum","合计：");
        }else {
            mapTotal.put("deptNumber","");
            mapTotal.put("userNumber","");
            mapTotal.put("userName", "");
            mapTotal.put("userIdentityCardNumber", "");
            mapTotal.put("startDate","");
            mapTotal.put("endDate","");
            mapTotal.put("sum","合计：");
        }

        for (int i = minYear; i <= maxYear; i++) {
            // 每月数据
            for (int j = 1; j <= 12; j++) {
                String monthStr = "";
                if (j < 10) {
                    monthStr = "0" + j;
                } else {
                    monthStr = "" + j;
                }
                String fees = i + monthStr;
                mapTotal.put(fees, "0");
            }
            mapTotal.put(i + "合计", "0");
        }
        return mapTotal;
    }
}





